package work.vcloud.baseinfo.utils;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;

public class ExportExcelUtil {
	
	/** 
     * 设置某些列的值只能输入预制的数据,显示下拉框. 
     *  
     * @param sheet 
     *            要设置的sheet. 
     * @param textlist 
     *            下拉框显示的内容 
     * @param firstRow 
     *            开始行 
     * @param endRow 
     *            结束行 
     * @param firstCol 
     *            开始列 
     * @param endCol 
     *            结束列 
     * @return 设置好的sheet. 
     */  
    public static XSSFSheet setXSSFValidation(XSSFSheet sheet,  
            String[] textlist, int firstRow, int endRow, int firstCol,  
            int endCol) {
    	
    	XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
    	XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
    			.createExplicitListConstraint(textlist);
        // 加载下拉列表内容  
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列  
        CellRangeAddressList regions = new CellRangeAddressList(firstRow,  
                endRow, firstCol, endCol);  
        // 数据有效性对象  
        XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
        sheet.addValidationData(data_validation_list);  
        return sheet;  
    }  
  
    /** 
     * 设置单元格上提示 
     *  
     * @param sheet 
     *            要设置的sheet. 
     * @param promptTitle 
     *            标题 
     * @param promptContent 
     *            内容 
     * @param firstRow 
     *            开始行 
     * @param endRow 
     *            结束行 
     * @param firstCol 
     *            开始列 
     * @param endCol 
     *            结束列 
     * @return 设置好的sheet. 
     */  
    public static HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptTitle,  
            String promptContent, int firstRow, int endRow, int firstCol,  
            int endCol) {  
        // 构造constraint对象  
        DVConstraint constraint = DVConstraint  
                .createCustomFormulaConstraint("BB1");  
        // 四个参数分别是：起始行、终止行、起始列、终止列  
        CellRangeAddressList regions = new CellRangeAddressList(firstRow,  
                endRow, firstCol, endCol);  
        // 数据有效性对象  
        HSSFDataValidation data_validation_view = new HSSFDataValidation(  
                regions, constraint);  
        data_validation_view.createPromptBox(promptTitle, promptContent);  
        sheet.addValidationData(data_validation_view);  
        return sheet;  
    }  
    
    public static HSSFDataValidation getDataValidationList4Col(HSSFSheet sheet, int firstRow,
    		int endRow, int firstCol,
    		int endCol,  String[] dataArray,
            HSSFWorkbook wbCreat) {
		HSSFSheet hidden = wbCreat.createSheet("hidden"+firstCol);
		
		HSSFCell cell = null;
		for (int i = 0, length = dataArray.length; i < length; i++)
		{
			String name = dataArray[i];
			HSSFRow row = hidden.createRow(i);
			cell = row.createCell(i);
			cell.setCellValue(name);
		}
		
		Name namedCell = wbCreat.createName();
		namedCell.setNameName("hidden"+endRow);
		namedCell.setRefersToFormula("hidden!$A$1:$A$" + dataArray.length);
		//加载数据,将名称为hidden的
		DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden"+endRow);
		
		// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
		CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol,
		endCol);
		HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
		
		//将第二个sheet设置为隐藏
		wbCreat.setSheetHidden(1, true);
		
		if (null != validation) {
			sheet.addValidationData(validation);
		}
		return validation;
	}
    
    // 创建下拉列表值存储工作表并设置值
    public static void genearteOtherSheet(Workbook wb, String[] typeArrays, int col) {
        // 创建下拉列表值存储工作表
        Sheet sheet = wb.createSheet("sheet"+col);
        
        // 循环往该sheet中设置添加下拉列表的值
        for (int i = 0; i < typeArrays.length; i++) {
            Row row = sheet.createRow(i);
            Cell cell = row.createCell(0);
            cell.setCellValue(typeArrays[i]);
        }
    }

    // 设置并引用其他Sheet作为绑定下拉列表数据
    public static DataValidation SetDataValidation(Workbook wb, String strFormula, int firstRow, int firstCol, int endRow, int endCol) {
        // 表示A列1-59行作为下拉列表来源数据
        // String formula = "sheet!$A$1:$A$59" ;
        // 原顺序为 起始行 起始列 终止行 终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        DataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)wb.getSheet("sheet"+firstCol));
        DataValidationConstraint formulaListConstraint = dvHelper.createFormulaListConstraint(strFormula);
        DataValidation dataValidation = dvHelper.createValidation(formulaListConstraint, regions);

        return dataValidation;
    }
    
	//将查询出来的结果写入excel表格中	
	public static void writeExcel(List<Map<String, Object>> headList, List<Map<String, Object>> dataList, String filePath, String fileName) throws Exception{
        // 执行导出逻辑
        XSSFWorkbook hssfWorkbook = new XSSFWorkbook();

        // 创建sheet页
        XSSFSheet sheet = hssfWorkbook.createSheet("数据");
        
        // 创建表头
        XSSFRow row0 = sheet.createRow(0);
        
    	for (int i = 0; i < headList.size(); i++) {
    		XSSFCell cell = row0.createCell(i); 
    		XSSFRichTextString text = new XSSFRichTextString(headList.get(i).get("name").toString());
    		cell.setCellValue(text);
		}
        
        for(int i = 0; i < dataList.size(); i++) {
    	   sheet.createRow(i+1);
    	}
        
        // 创建各行数据
        for (int i = 0; i < headList.size(); i++) {
			String colString = headList.get(i).get("col").toString();
			for (int j = 0; j < dataList.size(); j++) {
				XSSFRow row = sheet.getRow(j + 1);
				Map<String, Object> dataMap = dataList.get(j);
				if(dataMap.get(colString) != null) {
					row.createCell(i).setCellValue(dataMap.get(colString).toString());
				}else {
					row.createCell(i).setCellValue("");
				}
			}
		}

        FileUtil.createFile(filePath, fileName);
        OutputStream outputStream = new FileOutputStream(filePath+fileName);
        hssfWorkbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
	}
	
	//将查询出来的结果写入excel表格中	--表头导出
	public static void writeExcel(List<Map<String, Object>> headList, String filePath, String fileName) throws Exception{
        // 执行导出逻辑
        XSSFWorkbook hssfWorkbook = new XSSFWorkbook();

        // 创建sheet页
        XSSFSheet sheet = hssfWorkbook.createSheet("数据");
        
        // 创建表头
        XSSFRow row0 = sheet.createRow(0);
        XSSFRow row1 = sheet.createRow(1);
        XSSFCellStyle style = hssfWorkbook.createCellStyle(); // 样式对象  
        XSSFFont font = hssfWorkbook.createFont();
        font.setColor(HSSFColor.RED.index);//HSSFColor.VIOLET.index //字体颜色
        style.setFont(font);

		XSSFCellStyle textStyle = hssfWorkbook.createCellStyle();
		XSSFDataFormat format = hssfWorkbook.createDataFormat();
		textStyle.setDataFormat(format.getFormat("@"));

    	for (int i = 0; i < headList.size(); i++) {
    		XSSFCell cell = row0.createCell(i);
    		String ifnullString = headList.get(i).get("ifnull").toString();
    		if(ifnullString.equals("1")) {
    			cell.setCellStyle(style);
    		}

			//判断是否要设置为文本型
    		if (isText(headList, i)){
				sheet.setDefaultColumnStyle(i, textStyle);
			}

    		XSSFRichTextString text = new XSSFRichTextString(headList.get(i).get("name").toString());
    		cell.setCellValue(text);
		}
    	for (int i = 0; i < headList.size(); i++) {
    		Map<String, Object> head = headList.get(i);
    		XSSFCell cell = row1.createCell(i);
			//判断是否要设置为文本型
			if (isText(headList, i)){
				cell.setCellStyle(textStyle);
			}

    		if(head.get("fk_form")!=null || head.get("fk_rule")!=null) {
    			List<Map<String, Object>> aList = (List<Map<String, Object>>) head.get("list");
    			if(aList != null) {
    				if(aList.size() > 0) {
    					String[] strArr = new String[aList.size()];
    					for(int j = 0;j < aList.size(); j++) {
    						String str = aList.get(j).get("name").toString();
    						strArr[j] = str;
    					}
    					//sheet = setXSSFValidation(sheet, strArr, 1, 2, i, i);
    					//getDataValidationList4Col(sheet, 1, 2, i, i, strArr, hssfWorkbook);
    					genearteOtherSheet(hssfWorkbook, strArr, i);
    					sheet.addValidationData(SetDataValidation(hssfWorkbook, "sheet"+i+"!$A$1:$A$" + strArr.length, 1, i, 1, i));
    					// 隐藏作为下拉列表值的Sheet
    		        	hssfWorkbook.setSheetHidden(hssfWorkbook.getSheetIndex("sheet"+i), 1);
    				}
    			}else {
    				if(headList.get(i).containsKey("remarks")) {
    		   			XSSFRichTextString text = new XSSFRichTextString(headList.get(i).get("remarks").toString());
    		    		cell.setCellValue(text);
    	   			}
    			}
	   		}else {
	   			if(headList.get(i).containsKey("remarks")) {
		   			XSSFRichTextString text = new XSSFRichTextString(headList.get(i).get("remarks").toString());
		    		cell.setCellValue(text);
	   			}
	   		}
		}
        
        FileUtil.createFile(filePath, fileName);
        OutputStream outputStream = new FileOutputStream(filePath+fileName);
        hssfWorkbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
	}

	/**
	 * 判断是否要设置为文本型
	 * @param headList
	 * @param i
	 * @return
	 */
	private static boolean isText(List<Map<String, Object>> headList, int i) {
		return "电话".equals(headList.get(i).get("name")) || "身份证件号".equals(headList.get(i).get("name"))
		|| "办公电话（内）".equals(headList.get(i).get("name"))|| "从教年月".equals(headList.get(i).get("name"))
				|| "出生日期".equals(headList.get(i).get("name"));
	}

}
